<?php
class Opsurats extends CI_Model{
	function getData($no='',$client='',$start='',$end=''){
		$sql = " SELECT * FROM opinions ";
		$sql.= " WHERE no_op<>'' ";
		if(!empty($no)):
			$sql.= " AND no_op LIKE '%".$no."%' ";
		endif;
		if(!empty($client)):
			$sql.= " AND nama_klien LIKE '%".$client."%' ";
		endif;
		if(!empty($start)):
			$sql.= " AND tanggal >='".$start."' AND tanggal<='".$end."'";
		endif;
		$sql.= " ORDER BY ABS(no) DESC ";
		$q=$this->db->query($sql);
		if($q->num_rows()>0) {
			return $q->result_array();
			$q->free_result();
		}
		else{
			//return false;
		}
	}
	
	function getLatestPK($id){
		$sql = " SELECT ".$id." FROM opinions WHERE kap='".$this->session->userdata('kap')."'";
		$sql.= " ORDER BY ABS(".$id.") DESC LIMIT 1 ";
		//echo $sql;
		$q=$this->db->query($sql);
		$row=$q->row_array();
		if($q->num_rows()>0):
			$data=$row['no']+1;
			if($data<=9):
				$data = '00'.$data;
			elseif(($data>=10) AND ($data<=99) ):
				$data = '0'.$data;
			elseif(($data>=99) AND ($data<=999) ):
				$data = $data;
			endif;	
		else:	
			$data='001';
		endif;
		return $data;
		
	}
	
	function getCountClient($kap,$tujuan,$client){
		$sql = " SELECT COUNT(*) as total FROM opinions WHERE kap='".$kap."' AND init_klien='".$client."' AND tujuan='".$tujuan."'";
		//$sql.= " ORDER BY ABS(".$id.") DESC LIMIT 1 ";
		echo $sql;
		$q=$this->db->query($sql);
		$row=$q->row_array();
		$data=$row['total']+1;
		if($data > 3){
			$this->session->set_flashdata('message','<b style="color:red"><i>Partner '.$tujuan.' untuk '.$client.' Sudah Melebihi 3X</i><b/>');
			redirect('opsurat/index/'); }
		 else {
		return $data; }	
	}
	
	
	function getSave(){
		
		$data=$this->getCountClient($this->session->userdata('kap'),$this->input->post('tujuan'),$this->input->post('init_klien'));
		$no = $this->input->post('no').'/'.$this->session->userdata('code').'/'.$this->input->post('tujuan').'/'.to_romawi($data).'/'.$this->input->post('init_klien').'-'.$data.'/'.substr($this->input->post('year'),2,2);
		$val=array(
			'no_op' 	=>  $no,
			'kap'		=>	$this->session->userdata('kap'),
			'no'	   	=>  $this->input->post('no'),
			'type'	   	=>  'Opini',
            'tujuan'   	=>  $this->input->post('tujuan'),
			'tanggal'  	=>  $this->input->post('year').'-'.$this->input->post('month').'-'.$this->input->post('day'),
			'init_klien'=>	$this->input->post('init_klien'),
			'nama_klien'=>	$this->input->post('nama_klien'),
			'alamat'	=>	$this->input->post('alamat'),
			'keterangan'=>	$this->input->post('keterangan'),
			'pembuat'	=>	$this->session->userdata('name'),
			'is_trash'	=>	0	
		);
		$this->db->insert('opinions',$val);	
	}
	
	function getID($id){
		$sql = " SELECT *,DAY(tanggal) as day,MONTH(tanggal) as month,YEAR(tanggal) as year FROM opinions ";
		$sql.= " WHERE id = ".$id;
		$q=$this->db->query($sql);
		return $q->row_array();
		$q->free_result();
	}
	
	function getUpdate(){
		//$no = $this->input->post('no').'/'.'QUO'.'/'.$this->input->post('tujuan').'/'.'HES-'.$this->session->userdata('code').'/'.to_romawi($this->input->post('month')).'/'.substr($this->input->post('year'),2,2);
		$val=array(
			//'no_quo' 	=>  $no,
			//'no'	   	=>  $this->input->post('no'),
			'type'	   	=>  'Opini',
            //'tujuan'   	=>  $this->input->post('tujuan'),
			'tanggal'  	=>  $this->input->post('year').'-'.$this->input->post('month').'-'.$this->input->post('day'),
			//'init_klien'=>	$this->input->post('init_klien'),
			//'nama_klien'=>	$this->input->post('nama_klien'),
			'alamat'	=>	$this->input->post('alamat'),
			'keterangan'=>	$this->input->post('keterangan'),
			'pembuat'	=>	$this->session->userdata('name'),
			'is_trash'	=>	0	
		);
		$this->db->where('id',$this->input->post('id'));
		$this->db->update('opinions',$val);	
	}
	
	function getRemove($id){
		$this->db->where('id',$id);
		$this->db->delete('opinions');
	}
	
	function getDataRE($no='',$client='',$start='',$end=''){
		$sql = " SELECT *,rev.id FROM opinions_rev rev ";
		$sql.= " JOIN opinions o ON o.id=rev.op_id ";
		$sql.= " WHERE o.no_op<>'' ";
		if(!empty($no)):
			$sql.= " AND o.no_op LIKE '%".$no."%' ";
		endif;
		if(!empty($client)):
			$sql.= " AND o.nama_klien LIKE '%".$client."%' ";
		endif;
		if(!empty($start)):
			$sql.= " AND rev.tanggal >='".$start."' AND rev.tanggal<='".$end."'";
		endif;
		$sql.= " ORDER BY ABS(rev.id) DESC ";
		$q=$this->db->query($sql);
		if($q->num_rows()>0) {
			return $q->result_array();
			$q->free_result();
		}
		else{
			//return false;
		}
	}
	
	function getSaveRE(){
		$val=array(
			'op_id' 	=>  $this->input->post('id'),
			'tanggal'  	=>  $this->input->post('year').'-'.$this->input->post('month').'-'.$this->input->post('day'),
			'keterangan'=>	$this->input->post('keterangan'),
			'pembuat'	=>	$this->session->userdata('name'),
			'is_trash'	=>	0	
		);
		$this->db->insert('opinions_rev',$val);	
	}
	
	function getRemoveRE($id){
		$this->db->where('id',$id);
		$this->db->delete('opinions_rev');
	}
}